package com.g2room.driving.generate.kc890;

import java.awt.image.BufferedImage;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.io.StringWriter;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.imageio.ImageIO;

import org.jsoup.Jsoup;

import com.g2room.driving.generate.to.ArticleTo;
import com.g2room.driving.model.po.Category;
import com.g2room.driving.util.Constants;
import com.g2room.driving.util.DbUtil;
import com.g2room.driving.util.HtmlUtil;
import com.g2room.driving.util.ImageUtil;

import freemarker.template.Configuration;
import freemarker.template.Template;

public class ReleaseKc890Article {
/**
 *
update d_article_kc890 a,d_category b set a.category_code=b.kc_code where a.category_id=b.id;
update d_article_kc890 a,d_category b set a.type_code=b.kc_code where a.type_id=b.id;
 */
//	private static Logger logger = Logger.getLogger(CreateHtmlByFtl.class);
	// 台式机路径
	private static String basePath = Constants.basePath+ "kc890/";
	private static String baseHtmlPath = Constants.baseHtmlPath + "kc890/";
	private static String localImgDir = Constants.localImgDir + "driving/targetImg/";
	private static String remoteImgDir = "http://image.kc890.com/";
	private static Map<Long, Category> cats = new HashMap<Long, Category>();
	public static String publishHtmlPath = null;
	static int createFlag = -1;
	static String name = "kc890";
	
	public static void createHtml() {
		 @SuppressWarnings("deprecation")
		 Configuration cfg = new Configuration();
		 try {
			 int updateId = 3;
			 String publishTime = DbUtil.getPublishTimeStr(updateId);
			 cfg.setDirectoryForTemplateLoading(new File(basePath));
			 System.out.println(name + "生成主页！");
			 createIndex(cfg);
			 
			 System.out.println(name + "生成最新最热页！");
			 createLatest(cfg);
			 System.out.println(name + "生成列表页！");
			 createList(cfg, publishTime);
			 
			 System.out.println(name + "生成详情页！");
			 createDetail(cfg, publishTime);
			 DbUtil.updateTime(updateId);
			// 关闭连接
			DbUtil.closeConn();
		} catch (IOException e) {
			System.out.println(name + "发生异常：" + e.getMessage());
			e.printStackTrace();
		}  
	}
	public static void createIndex(Configuration cfg) {
		try {
			Map<String, Object> articleMap = new HashMap<String, Object>();
			// 获取幻灯片，返回6条数据
			articleMap.put("slides", getArticlesImgByCategoryId(null, 0, 400, 1.9, 0.4, 5, true));
			// 获取带摘要的数据，只有一条
			List<ArticleTo> artis = getStickSummary();
			articleMap.put("stickSummary", artis);
			String ids = getIds(artis);
			List<ArticleTo> artisSticks = getSticks(ids);
			ids += "," + getIds(artisSticks);
			//  获取最热数据，取最新的8条数据
			articleMap.put("sticks", artisSticks);
			
			// 新手开车数据，图两张，列表数据10条
			// String field, int cId, int limit, int imgNum, Integer w, Integer h, Integer fluc
			articleMap.put("beginnerImg", getArticlesImgByCategoryId("category_id", 2, 100, 1.5, 0.4, 2));
			articleMap.put("beginners", getArticlesByCategoryId("category_id", 2, 10, null));
			// 汽车养护，图两张，列表数据10条
			articleMap.put("maintainImg", getArticlesImgByCategoryId("category_id", 16, 100, 1.5, 0.4, 2));
			articleMap.put("maintains", getArticlesByCategoryId("category_id", 16, 10, null));
			// 车险知识，图1张，5条记录
			articleMap.put("autoinsuImg", getArticlesImgByCategoryId("category_id", 19, 190, 2, 0.5, 1));
			articleMap.put("autoinsus", getArticlesByCategoryId("category_id", 19, 5, null));
			// 交通法规，图1张，7条记录
//			articleMap.put("lawImg", getArticlesImgByCategoryId("category_id", 6, 190, 2, 0.5, 1));
			articleMap.put("laws", getArticlesByCategoryId("category_id", 6, 7, null));
			// 汽车知识，图1张带照摘要，5条记录
			articleMap.put("knowledgeImg", getArticlesImgByCategoryId("category_id", 17, 90, 2, 0.5, 1));
			articleMap.put("knowledges", getArticlesByCategoryId("category_id", 17, 5, null));
			// 女性开车，图2张，10条记录
			articleMap.put("womenImg", getArticlesImgByCategoryId("category_id", 4, 100, 1.5, 0.4, 2));
			articleMap.put("womens", getArticlesByCategoryId("category_id", 4, 10, null));
			// 自动挡，图1张，10条记录
			articleMap.put("autocarImg", getArticlesImgByCategoryId("category_id", 3, 220, 2, 0.5, 1));
			articleMap.put("autocars", getArticlesByCategoryId("category_id", 3, 10, null));
			
			List<ArticleTo> skills = getArticlesByCategoryId("category_id", 1, 12, null);
			List<List<ArticleTo>> skillArtis = new ArrayList<List<ArticleTo>>();
			skillArtis.add(skills.subList(0, 3));
			skillArtis.add(skills.subList(3, 6));
			skillArtis.add(skills.subList(6, 9));
			skillArtis.add(skills.subList(9, 12));
			
			// banner驾车技巧，12条记录分三组显示
			articleMap.put("skills", skillArtis);
			
			// 停车技巧，图3张，带摘要，12条记录
			articleMap.put("parkImg", getArticlesImgByCategoryId("type_id", 7, 220, 1.5, 0.4, 3));
			articleMap.put("parks", getArticlesByCategoryId("type_id", 7, 12, null));
			// 刹车技巧，图3张，带摘要，12条记录
			articleMap.put("brakeImg", getArticlesImgByCategoryId("type_id", 9, 220, 1.5, 0.4, 3));
			articleMap.put("brakes", getArticlesByCategoryId("type_id", 9, 12, null));

			// 恶劣天气，图1张，6条记录
			articleMap.put("weatherImg", getArticlesImgByCategoryId("type_id", 13, 250, 2, 0.5, 1));
			articleMap.put("weathers", getArticlesByCategoryId("type_id", 13, 7, null));

			// 决战高速，图1张，6条记录
			articleMap.put("freewayImg", getArticlesImgByCategoryId("type_id", 15, 250, 2, 0.5, 1));
			articleMap.put("freeways", getArticlesByCategoryId("type_id", 15, 7, null));
			// 夜间开车
			articleMap.put("nights", getArticlesByCategoryId("type_id", 12, 13, null));
			// 驾车安全，10条数据
			articleMap.put("safes", getArticlesByCategoryId("category_id", 5, 10, null));
			// 省油技巧，10条数据
			articleMap.put("savefuels", getArticlesByCategoryId("category_id", 18, 10, null));

			// 技巧图解，9张图片，单独获取
			articleMap.put("imges", getImages(ids));
			
			Template tmpl = null;
			StringWriter out = null;
			try {
				System.out.println(name + "加载模板文件。");
				tmpl = cfg.getTemplate("index.ftl");
				out = new StringWriter();
				tmpl.process(articleMap, out);
				System.out.println(name + "本次生成文件路径："+HtmlUtil.getPath(baseHtmlPath));
				// 生成Mapper文件
				createFile(out.toString(), "index.html");
			} catch (Exception e) {
				System.out.println(name + "生成主页页面异常：" + e.getMessage());
				e.printStackTrace();
			} finally {
				try {
					if (null != out)
						out.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		} catch (Exception e) {
			System.out.println(name + "生成主页异常：" + e.getMessage());
			e.printStackTrace();
		}
	}
	
	public static void createLatest(Configuration cfg) {
		try {
			Map<String, List<ArticleTo>> articleMap = new HashMap<String, List<ArticleTo>>();
			// 获取全局最新文章
			articleMap.put("latestArticles", getLatestArtis(10));
			articleMap.put("hotArticles", getArticlesImgByCategoryId(null, 0, 90, 1.3, 0.3, 10, true));
			StringWriter out = null;
			try {
				Template tmpl = cfg.getTemplate("latest.ftl");
				out = new StringWriter();
				tmpl.process(articleMap, out);
				// 生成文件
				createFile(out.toString(), "latest.html");
			} catch (Exception e) {
				System.out.println(name + "发生异常：" + e.getMessage());
				e.printStackTrace();
			} finally {
				try {
					if (null != out)
						out.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	public static void createDetail(Configuration cfg, String publishTime) {
		try {
			if(null == cats || cats.isEmpty()) getTypes();
			Map<String, List<ArticleTo>> articlesMap = new HashMap<String, List<ArticleTo>>();
			List<ArticleTo> articles = getDetails(publishTime);
			System.out.println(name + "详情文章数：" + articles.size());
			Template tmpl = cfg.getTemplate("detail.ftl");
			StringWriter out = null;
			Map<String, Object> articleMap = new HashMap<String, Object>();
			for(ArticleTo arti : setCatName(articles)) {
				try {
					String code = (null == arti.getTypeCode() || arti.getTypeCode().length()==0) ? arti.getCategoryCode() : arti.getTypeCode();
					List<ArticleTo> articleList = articlesMap.get(code);
					if(null == articleList || articleList.size() == 0) {
						articleList = new ArrayList<ArticleTo>();
						articleList = getArticlesByCategoryId("category_id", arti.getCategoryId(), 12, publishTime);
						articlesMap.put(code, articleList);
					}

					articleMap.put("keywords", cats.get(arti.getCategoryId()).getKeywords());
					articleMap.put("descMeta", cats.get(arti.getCategoryId()).getDescMeta());
					
					articleMap.put("article", arti);
					articleMap.put("recomms", articleList);
					out = new StringWriter();
					tmpl.process(articleMap, out);
					String catCode = arti.getCategoryCode();
					// 生成文件
					createFile(out.toString(), catCode + "/d"+ arti.getTypeCode() + arti.getId() +".html");
					if(createFlag == 0) return;
				} catch (Exception e) {
					e.printStackTrace();
				} finally {
					try {
						if (null != out)
							out.close();
					} catch (Exception e) {
						e.printStackTrace();
					}
				}
			}
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	public static void createList(Configuration cfg, String publishTime) {
		try {
			if(null == cats || cats.isEmpty()) getTypes();
			int pageSize = 10;
			Map<Long, List<ArticleTo>> catHopArtisImgMaps = new HashMap<Long, List<ArticleTo>>();
			Map<Long, List<ArticleTo>> catHopArtisMaps = new HashMap<Long, List<ArticleTo>>();
			List<ArticleTo> allHopArtisImgs = getArticlesImgByCategoryId(null, 0, 90, 1.3, 0.3, 8, true);
			for(long cid : cats.keySet()) {
				Map<String, Object> articleMap = new HashMap<String, Object>();
				int count = 0 != cats.get(cid).getParentId() ? getArticlesCountByTypeId(cid, publishTime) : getArticlesCountByCategoryId(cid, publishTime);
				List<ArticleTo> articleList = null;
				if(count == 0) {
					continue;
				} else {
					String cids = String.valueOf(cid);
					if(0 != cats.get(cid).getParentId()) {
						// 二级分类不生成列表
//						continue;
						articleList = getArticlesByCategoryId("type_id", cids, 100000, null, true, 1.67, 0.4, 150);
						catHopArtisImgMaps.put(cid, getArticlesImgByCategoryId("type_id", cid, 100, 2.08, 0.4, 1));
						// 获取本版最热的数据
						catHopArtisMaps.put(cid, getHotArticles(cid, 5));
					} else {
						// 合并女性开车和自动挡到开车技巧数据
						if(cid == 1) {
							cids += ",3,4";
						}
						articleList = getArticlesByCategoryId("category_id", cids, 100000, null, true, 1.67, 0.4, 150);
						catHopArtisImgMaps.put(cid, getArticlesImgByCategoryId("category_id", cid, 100, 2.08, 0.4, 1));
						// 获取本版最热的数据
						catHopArtisMaps.put(cid, getHotArticles(cid, 5));
					}
				}
				// 获取本版最热文章，只有一张图片
				if(catHopArtisImgMaps.isEmpty()) {
					System.out.println(name + "获取分类顺序错误，未获取到顶级分类！");
					return;
				}
				articleMap.put("allHopArtisImg", allHopArtisImgs);
				articleMap.put("catHotArtisImg", catHopArtisImgMaps.get(cid));
				articleMap.put("catHotArtis", catHopArtisMaps.get(cid));
				Category category = cats.get(cid);
				for(int start = 0; (start) * pageSize < articleList.size(); start++) {
					articleMap.put("articles", articleList.subList(start * pageSize, ((start + 1) * pageSize >= articleList.size() ? articleList.size() : (start + 1) * pageSize)));
					
					if(null == category.getParentId() || category.getParentId() == 0) {
						articleMap.put("categoryName", category.getName());
						articleMap.put("categoryCode", category.getCode());
					} else {
						articleMap.put("categoryName", cats.get(category.getParentId().longValue()).getName());
						articleMap.put("categoryCode", cats.get(category.getParentId().longValue()).getCode());
						articleMap.put("typeName", category.getName());
						articleMap.put("typeCode", category.getCode());
					}
					
					articleMap.put("keywords", category.getKeywords());
					articleMap.put("descMeta", category.getDescMeta());
					articleMap.put("summary", category.getSummary());
					articleMap.put("count", articleList.size());
					articleMap.put("pageSize", pageSize);
					articleMap.put("pageNo", start);
					
					Template tmpl = cfg.getTemplate("list.ftl");
					StringWriter out = null;
					try {
						out = new StringWriter();
						tmpl.process(articleMap, out);
						if(start==0) {
							createFile(out.toString(), category.getCode() + "/index.html");
						}
						createFile(out.toString(), category.getCode() + "/list_"+ category.getCode() + "_" + (start+1) +".html");
						if(createFlag == 0) return;
					} catch (Exception e) {
						e.printStackTrace();
					} finally {
						try {
							if (null != out)
								out.close();
						} catch (Exception e) {
							e.printStackTrace();
						}
					}
				}
			}
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 设置类别里面的类型名称
	 * @param artis
	 * @return
	 */
	private static List<ArticleTo> setCatName(List<ArticleTo> artis) {
		if(null == artis) return artis;
		for(ArticleTo arti : artis) {
			Category cat = cats.get(arti.getCategoryId());
			arti.setCategoryId(cat.getId());
			arti.setCategoryCode(cat.getCode());
			arti.setCategoryName(cat.getName());
			if(null != arti.getTypeId() && arti.getTypeId() > 0) {
				Category typeCat = cats.get(arti.getTypeId());
				arti.setTypeId(typeCat.getId());
				arti.setTypeCode(typeCat.getCode());
				arti.setTypeName(typeCat.getName());
			}
		}
		
		return artis;
	}
	
	/**
	 * 获取技巧图解的9张图
	 * @return
	 */
	public static List<ArticleTo> getImages(String ids) {

		List<ArticleTo> imgArticles1 = new ArrayList<ArticleTo>();
		List<ArticleTo> imgArticles2 = new ArrayList<ArticleTo>();
		List<ArticleTo> imgArticles3 = new ArrayList<ArticleTo>();
		List<ArticleTo> imgArticles4 = new ArrayList<ArticleTo>();
		
		List<ArticleTo> articles = new ArrayList<ArticleTo>();
		try {
			Connection connection = DbUtil.getConn();
			Statement statement = connection.createStatement();
			String sql = "SELECT a.id,a.title,a.publish_time,a.category_id,a.category_code,a.category_name,a.type_id,a.type_code,a.type_name,a.content,a.summary FROM d_article_kc890 a where "
					+ " publish_time < '"+HtmlUtil.getNow()+"' and state=1 and hot =1 and id not in (" + ids 
					+ ") and content like '%<img%' order by a.publish_time desc  limit 200";
			ResultSet rs = statement.executeQuery(sql);
			while (rs.next()) {
				ArticleTo articleTo = new ArticleTo();
				articleTo.setId(rs.getLong("id"));
				articleTo.setTitle(rs.getString("title"));
				articleTo.setPublishTime(rs.getDate("publish_time"));
				articleTo.setCategoryId(rs.getLong("category_id"));
				articleTo.setCategoryCode(rs.getString("category_code"));
				articleTo.setCategoryName(rs.getString("category_name"));
				articleTo.setTypeId(rs.getLong("type_id"));
				articleTo.setTypeName(rs.getString("type_name"));
				String typeCode = rs.getString("type_code");
				articleTo.setTypeCode(isEmpty(typeCode) ? articleTo.getCategoryCode() : typeCode);
				articleTo.setSummary(rs.getString("summary"));
				String content = rs.getString("content");
				// 提取src值
				List<String> imgSrcs = ImageUtil.getImgSrc(content);
				if(null == articleTo.getSummary() || articleTo.getSummary().length() < 1) {
					content = HtmlUtil.delHTMLTag(content);
					articleTo.setSummary(null == content || content.length() ==0 ? "" : content.substring(0, Math.min(200, content.length())));
				}
				if(imgSrcs.isEmpty()) {
					continue;
				} else {
					for(String imgSrc : imgSrcs) {
						if(!imgSrc.startsWith(remoteImgDir)
								&& !imgSrc.contains("kc890.com")
							) continue;
//						if(!imgSrc.startsWith(localImgDir)) continue;
						try {
							double imgPr = getPr(imgSrc, articleTo);
//							imgSrc = replaceImgSrc(imgSrc);
							/**
							 Map<Double, Integer> flucs = new HashMap<Double, Integer>();
								flucs.put(1.34, 280); // 第1张
								flucs.put(1.34, 150); // 第2 3 5 6 9张
								flucs.put(2.7, 250); // 第4张
								flucs.put(0.67, 150); // 第7 8张
							 */
					        if(imgArticles1.size() < 1 && rangeInDefined(imgPr, 1.34-0.3, 1.34+0.3) && articleTo.getImgWidth() >= 280) {
					        	// 此种类型的图片取1张
					        	articleTo.setMainImg(imgSrc);
					        	imgArticles1.add(articleTo);
					        	break;
					        } else if(imgArticles2.size() < 5 && rangeInDefined(imgPr, 1.34-0.3, 1.34+0.3) && articleTo.getImgWidth() >= 150) {
					        	// 此种类型的图片取5张
					        	articleTo.setMainImg(imgSrc);
					        	imgArticles2.add(articleTo);
					        	break;
					        } else if(imgArticles3.size() < 1 && rangeInDefined(imgPr, 2.7-0.5, 2.7+0.5) && articleTo.getImgWidth() >= 250) {
					        	// 此种类型的图片取1张
					        	articleTo.setMainImg(imgSrc);
					        	imgArticles3.add(articleTo);
					        	break;
					        } else if(imgArticles4.size() < 2 && rangeInDefined(imgPr, 0.67-0.1, 0.67+0.1) && articleTo.getImgWidth() >= 150) {
					        	// 此种类型的图片取2张
					        	articleTo.setMainImg(imgSrc);
					        	imgArticles4.add(articleTo);
					        	break;
					        }
						} catch (Exception e) {
							e.printStackTrace();
							continue;
						}
					}
				}
				articles.add(articleTo);
			}
			rs.close();
			statement.close();
			// 如果符合的尺寸不够，则滥竽充数
			/**
			 Map<Double, Integer> flucs = new HashMap<Double, Integer>();
				flucs.put(1.34, 280); // 第1张
				flucs.put(1.34, 150); // 第2 3 5 6 9张
				flucs.put(2.7, 250); // 第4张
				flucs.put(0.67, 150); // 第7 8张
			 */
			if(imgArticles1.size() < 1) {
				imgArticles1.addAll(getImgByWidth(articles, 280, 1));
			}
			if(imgArticles2.size() < 5) {
				imgArticles2.addAll(getImgByWidth(articles, 150, 5 - imgArticles2.size()));
			}
			if(imgArticles3.size() < 1) {
				imgArticles3.addAll(getImgByWidth(articles, 250, 1));
			}
			if(imgArticles4.size() < 2) {
				imgArticles4.addAll(getImgByWidth(articles, 150, 2 - imgArticles1.size()));
			} 
		} catch (Exception e) {
			System.out.println(name + "获取数据发生异常：" + e.getMessage());
			e.printStackTrace();
			// 关闭连接
			DbUtil.closeConn();
		}

		List<ArticleTo> imgArticles = new ArrayList<ArticleTo>();
		imgArticles.addAll(imgArticles1);
		imgArticles.addAll(imgArticles2.subList(0, 2));
		imgArticles.addAll(imgArticles3);
		imgArticles.addAll(imgArticles2.subList(2, 4));
		imgArticles.addAll(imgArticles4);
		imgArticles.addAll(imgArticles2.subList(4, 5));
		return imgArticles;
	}
	
	public static List<ArticleTo> getArticlesImgByCategoryId(String field, long categoryId, int w, double pr, double fluc, int imgNum) {
		return getArticlesImgByCategoryId(field, categoryId, w, pr, fluc, imgNum, false);
	}
	
	/**
	 * 获取带图片的文章列表
	 * @param field	条件过滤字段
	 * @param categoryId	类别
	 * @param w	最小宽度
	 * @param pr	宽高比
	 * @param fluc	图片尺寸的宽高浮动范围
	 * @param imgNum	图片数
	 * @param hot	是否只获取热门数据
	 * @return
	 */
	public static List<ArticleTo> getArticlesImgByCategoryId(String field, long categoryId, int w, double pr, double fluc, int imgNum, boolean hot) {
		String sql = "SELECT a.id,a.title,a.publish_time,a.category_id,a.category_code,a.category_name,a.type_id,a.type_name,a.type_code,a.summary,a.content FROM d_article_kc890 a where "
				+ " publish_time < '"+HtmlUtil.getNow()+"' and state=1 "+(isEmpty(field) ? "" : " and "+field+"=" + categoryId) + (hot ? " and hot =1 " : "")
				+ " and content like '%<img%' and length(content) >1000 order by a.publish_time desc  limit 100";
		return getArtiHasImg(sql, w, pr, fluc, imgNum);
	}
	/**
	 * 获取含图片的文章
	 * @param sql	查询的SQL语句
	 * @param w	最小宽度
	 * @param pr	宽高比
	 * @param fluc	图片尺寸的宽高浮动范围
	 * @param imgNum	图片数
	 * @return
	 */
	private static List<ArticleTo> getArtiHasImg(String sql, int w, double pr, double fluc, int imgNum) {
		List<ArticleTo> articles = new ArrayList<ArticleTo>();
		List<ArticleTo> imgArticles = new ArrayList<ArticleTo>();
		try {
			Connection connection = DbUtil.getConn();
			Statement statement = connection.createStatement();
			ResultSet rs = statement.executeQuery(sql);
			while (rs.next()) {
				ArticleTo articleTo = new ArticleTo();
				articleTo.setId(rs.getLong("id"));
				articleTo.setTitle(rs.getString("title"));
				articleTo.setPublishTime(rs.getDate("publish_time"));
				articleTo.setCategoryId(rs.getLong("category_id"));
				articleTo.setCategoryCode(rs.getString("category_code"));
				articleTo.setCategoryName(rs.getString("category_name"));
				articleTo.setTypeId(rs.getLong("type_id"));
				String typeCode = rs.getString("type_code");
				articleTo.setTypeName(rs.getString("type_name"));
				articleTo.setTypeCode(isEmpty(typeCode) ? articleTo.getCategoryCode() : typeCode);
				articleTo.setSummary(rs.getString("summary"));
				String content = rs.getString("content");
				articleTo = getImgFromContent(content, articleTo, pr, fluc, w);
				if(null == articleTo.getSummary() || articleTo.getSummary().length() < 1) {
					articleTo.setSummary(articleTo.getContent());
				}
				// 没有图片
				if(articleTo.getImgWidth() == 0) {
					continue;
				}
				// 有主图
				if(!HtmlUtil.isEmpty(articleTo.getMainImg())) {
					imgArticles.add(articleTo);
					if(imgArticles.size() == imgNum) break;
					continue;
				}
				articles.add(articleTo);
			}
			rs.close();
			statement.close();
			// 如果尺寸不够，则滥竽充数
			if(imgArticles.size() < imgNum && articles.size() >= imgNum - imgArticles.size()) {
				for(ArticleTo arti : articles) {
					if(arti.getImgWidth() >= w) {
						imgArticles.add(arti);
					}
				}
			} 
		} catch (Exception e) {
			System.out.println(name + "获取数据发生异常：" + e.getMessage());
			e.printStackTrace();
			// 关闭连接
			DbUtil.closeConn();
		}
		return imgArticles;
	}
	/**
	 * 获取图片尺寸比率
	 * @param imgSrc
	 * @return
	 * @throws Exception
	 */
	private static double getPr(String imgSrc, ArticleTo articleTo) throws Exception {
		// 替换URL，从本地获取图片
		imgSrc = imgSrc.replace(remoteImgDir, localImgDir);
		File f = new File(imgSrc);
		double pr = 0;
	    try {
	    	BufferedImage sourceImg =ImageIO.read(new FileInputStream(f));
	    	if(null != sourceImg) {
	    		articleTo.setImgWidth(sourceImg.getWidth());
	    		pr = Double.valueOf(articleTo.getImgWidth()) / sourceImg.getHeight();
	    	}
	    } catch (Exception e) {
	    	System.out.println(name + "获取图片错误：" + imgSrc );
//	    	e.printStackTrace();
	    }
        return pr;
	}
	
	public static List<ArticleTo> getArticlesByCategoryId(String field, long categoryId, int limit, String publishTime) {
		return getArticlesByCategoryId(field, String.valueOf(categoryId), limit, publishTime, false, 0, 0, 0);
	}
	/**
	 * 根据类型查询数据，根据创建时间排序
	 * @param field	查询字段
	 * @param categoryId	类别ID
	 * @param limit	获取文章数量
	 * @param publishTime	发布时间
	 * @param content	是否获取内容
	 * @param pr	如果获取内容，则获取图片的宽高比
	 * @param fluc	图片宽高比浮动范围
	 * @param w	图片最小宽度
	 * @return
	 */
	public static List<ArticleTo> getArticlesByCategoryId(String field, String cids, int limit, String publishTime, 
			boolean cont, double pr, double fluc, int w) {
		List<ArticleTo> articles = new ArrayList<ArticleTo>();
		try {
			// 处理按大类搜索时，小类设置为空
			String sqlSub = "";
			if(!isEmpty(field)) {
				sqlSub = " and "+field+" in (" + cids + ") ";
			}
			Connection connection = DbUtil.getConn();
			Statement statement = connection.createStatement();
			String sql = "SELECT a.id,a.title,a.publish_time,a.category_id,a.category_code,a.category_name,a.type_id,a.type_code,a.type_name,a.summary"+( cont ? ",a.content" : "")+" FROM d_article_kc890 a "
					+ "where " + (isEmpty(publishTime) ? "" : "publish_time>'" + publishTime + "' and ") 
					+ " publish_time < '"+HtmlUtil.getNow()+"' and state=1 "+ sqlSub
					+ " order by a.publish_time desc  limit " + limit;
			 
			ResultSet rs = statement.executeQuery(sql);
			while (rs.next()) {
				ArticleTo articleTo = new ArticleTo();
				articleTo.setId(rs.getLong("id"));
				articleTo.setTitle(rs.getString("title"));
				articleTo.setPublishTime(rs.getDate("publish_time"));
				articleTo.setCategoryId(rs.getLong("category_id"));
				articleTo.setCategoryCode(rs.getString("category_code"));
				articleTo.setCategoryName(rs.getString("category_name"));
				articleTo.setTypeId(rs.getLong("type_id"));
				articleTo.setTypeName(rs.getString("type_name"));
				String typeCode = rs.getString("type_code");
				articleTo.setTypeCode(isEmpty(typeCode) ? articleTo.getCategoryCode() : typeCode);
				if(cont) {
					articleTo.setSummary(rs.getString("summary"));
					String content = rs.getString("content");
					articleTo = getImgFromContent(content, articleTo, pr, fluc, w);
					if(null == articleTo.getSummary() || articleTo.getSummary().length() < 1) {
						articleTo.setSummary(articleTo.getContent());
					}
				}
				articles.add(articleTo);
			}
			rs.close();
			statement.close();
		} catch (Exception e) {
			System.out.println(name + "获取数据发生异常：" + e.getMessage());
			e.printStackTrace();
			// 关闭连接
			DbUtil.closeConn();
		}
		return articles;
	}
	/**
	 * 从内容中提取出符合要求的图片
	 * @param content
	 * @param articleTo
	 * @param pr
	 * @param fluc
	 * @param w
	 * @return
	 */
	private static ArticleTo getImgFromContent(String content, ArticleTo articleTo, double pr, double fluc, int w) {
		// 提取src值
		List<String> imgSrcs = ImageUtil.getImgSrc(content);
//		Document doc = Jsoup.parse(content);
//		content = HtmlUtil.delHTMLTag(content);
		content = Jsoup.parse(content).text();
		if(null != content && content.length() > 0) {
			content = replaceContent(content);
			if(content.length()> 0) {
				content = content.substring(0, Math.min(200, content.length())).replaceAll("　", "");
			}
		}
		articleTo.setContent(content);
		if(imgSrcs.isEmpty()) return articleTo;
		
		for(String imgSrc : imgSrcs) {
			if(!imgSrc.startsWith(remoteImgDir)
					&& !imgSrc.contains("kc890.com")
					) continue;
			try {
				double imgPr = getPr(imgSrc, articleTo);
		        if(pr > 0 && rangeInDefined(imgPr, pr-fluc, pr+fluc) && articleTo.getImgWidth() >= w) {
		        	articleTo.setMainImg(imgSrc);
		        	break;
		        } else if(articleTo.getImgWidth() >= w){
		        	articleTo.setMainImg(imgSrc);
		        }
			} catch (Exception e) {
				e.printStackTrace();
				continue;
			}
		}
		return articleTo;
	}

	/**
	 * 获取带摘要数据的最热数据，只获取一条数据
	 * @return
	 */
	public static List<ArticleTo> getStickSummary() {
		List<ArticleTo> articles = new ArrayList<ArticleTo>();
		try {
			Connection connection = DbUtil.getConn();
			Statement statement = connection.createStatement();
//			String sql = "SELECT id,title,publish_time,category_id,category_name,summary,content,category_code,type_id,type_name,type_code FROM d_article_kc890 WHERE summary is not null and publish_time < '"+HtmlUtil.getNow()+"' and state=1 and hot=1 and id >= ((SELECT MAX(id) FROM d_article_kc890 where publish_time < '"+HtmlUtil.getNow()+"')-(SELECT MIN(id) FROM d_article_kc890 where publish_time < '"+HtmlUtil.getNow()+"')) * RAND() + (SELECT MIN(id) FROM d_article_kc890 where  publish_time < '"+HtmlUtil.getNow()+"') LIMIT 1";
			String sql = "SELECT id,title,publish_time,category_id,category_name,summary,content,category_code,type_id,type_name,type_code FROM d_article_kc890 WHERE summary is not null and publish_time < '"+HtmlUtil.getNow()+"' order by publish_time desc LIMIT 1";
			ResultSet rs = statement.executeQuery(sql);
			while (rs.next()) {
				ArticleTo articleTo = new ArticleTo();
				articleTo.setId(rs.getLong("id"));
				articleTo.setTitle(rs.getString("title"));
				articleTo.setPublishTime(rs.getDate("publish_time"));
				articleTo.setCategoryId(rs.getLong("category_id"));
				articleTo.setCategoryCode(rs.getString("category_code"));
				articleTo.setCategoryName(rs.getString("category_name"));
				articleTo.setTypeId(rs.getLong("type_id"));
				articleTo.setTypeName(rs.getString("type_name"));
				String typeCode = rs.getString("type_code");
				articleTo.setTypeCode(isEmpty(typeCode) ? articleTo.getCategoryCode() : typeCode);
				articleTo.setSummary(rs.getString("summary"));
				articles.add(articleTo);
			}
			rs.close();
			statement.close();
		} catch (Exception e) {
			System.out.println(name + "获取摘要数据发生异常：" + e.getMessage());
			e.printStackTrace();
			// 关闭连接
			DbUtil.closeConn();
		}
		return articles;
	}
	/**
	 * 获取最热数据，取最新的8条数据
	 * @return
	 */
	public static List<ArticleTo> getSticks(String ids) {
		List<ArticleTo> articles = new ArrayList<ArticleTo>();
		try {
			Connection connection = DbUtil.getConn();
			Statement statement = connection.createStatement();
			String sql = "SELECT a.id,a.title,a.publish_time,a.category_id,a.category_code,a.category_name,a.type_id,a.type_code,a.type_name FROM d_article_kc890 a where publish_time < '"+HtmlUtil.getNow()+"' and state=1 and hot=1 and id not in ("+ids+") order by a.publish_time desc  limit 8";
			ResultSet rs = statement.executeQuery(sql);
			while (rs.next()) {
				ArticleTo articleTo = new ArticleTo();
				articleTo.setId(rs.getLong("id"));
				articleTo.setTitle(rs.getString("title"));
				articleTo.setPublishTime(rs.getDate("publish_time"));
				articleTo.setCategoryId(rs.getLong("category_id"));
				articleTo.setCategoryCode(rs.getString("category_code"));
				articleTo.setCategoryName(rs.getString("category_name"));
				articleTo.setTypeId(rs.getLong("type_id"));
				articleTo.setTypeName(rs.getString("type_name"));
				String typeCode = rs.getString("type_code");
				articleTo.setTypeCode(isEmpty(typeCode) ? articleTo.getCategoryCode() : typeCode);
				articles.add(articleTo);
			}
			rs.close();
			statement.close();
		} catch (Exception e) {
			e.printStackTrace();
			// 关闭连接
			DbUtil.closeConn();
		}
		return articles;
	}
	

	/**
	 * 获取最热数据，取最新的n条数据
	 * @return
	 */
	public static List<ArticleTo> getHotArticles(long cid, int limit) {
		List<ArticleTo> articles = new ArrayList<ArticleTo>();
		try {
			Connection connection = DbUtil.getConn();
			Statement statement = connection.createStatement();
			String sql = "SELECT a.id,a.title,a.publish_time,a.category_id,a.category_code,a.category_name,a.type_id,a.type_code,a.type_name FROM d_article_kc890 a where publish_time < '"+HtmlUtil.getNow()+"' and state=1 and hot=1 "
					+ (cid==0 ? "" : "and category_id = " +cid) + "  order by a.publish_time desc  limit " + limit;
			ResultSet rs = statement.executeQuery(sql);
			while (rs.next()) {
				ArticleTo articleTo = new ArticleTo();
				articleTo.setId(rs.getLong("id"));
				articleTo.setTitle(rs.getString("title"));
				articleTo.setPublishTime(rs.getDate("publish_time"));
				articleTo.setCategoryId(rs.getLong("category_id"));
				articleTo.setCategoryCode(rs.getString("category_code"));
				articleTo.setCategoryName(rs.getString("category_name"));
				articleTo.setTypeId(rs.getLong("type_id"));
				articleTo.setTypeName(rs.getString("type_name"));
				String typeCode = rs.getString("type_code");
				articleTo.setTypeCode(isEmpty(typeCode) ? articleTo.getCategoryCode() : typeCode);
				articles.add(articleTo);
			}
			rs.close();
			statement.close();
		} catch (Exception e) {
			e.printStackTrace();
			// 关闭连接
			DbUtil.closeConn();
		}
		return articles;
	}


	public static int getArticlesCountByCategoryId(long categoryId, String publishTime) {
		return getArticlesCountByCategoryId("category_id", categoryId, publishTime);
	}
	
	public static int getArticlesCountByTypeId(long typeId, String publishTime) {
		return getArticlesCountByCategoryId("type_id", typeId, publishTime);
	}
	public static int getArticlesCountByCategoryId(String field, long categoryId, String publishTime) {
		int count = 0;
		try {
			Connection connection = DbUtil.getConn();
			Statement statement = connection.createStatement();
			String sql = "SELECT count(*) as count FROM d_article_kc890 a "
					+ "where " + (isEmpty(publishTime) ? "" : "publish_time >'" + publishTime + "' and ") 
					+ " publish_time < '"+HtmlUtil.getNow()+"' and state=1 and "+field+"=" + categoryId;
			ResultSet rs = statement.executeQuery(sql);
			while (rs.next()) {
				count = rs.getInt("count");
			}
			rs.close();
			statement.close();
		} catch (Exception e) {
			System.out.println(name + "获取统计数据发生异常：" + e.getMessage());
			e.printStackTrace();
			// 关闭连接
			DbUtil.closeConn();
		}
		return count;
	}
	
	public static List<ArticleTo> getDetails(String publishTime) {
		String sql = "select id,category_id, category_code, type_code,category_name, type_name,type_id,title,content,publish_time,summary from d_article_kc890 "
				+ "where publish_time>'" + publishTime + "' and publish_time < '"+HtmlUtil.getNow()+"' and state=1";
		return getDetailsData(sql);
	}
	public static List<ArticleTo> getLatestArtis(int limit) {
		String sql = "select a.* from d_article_kc890 a where publish_time < '"+HtmlUtil.getNow()+"' and state=1 and a.id in (select aa.id from (select b.id from d_article_kc890 b where b.publish_time < '"+HtmlUtil.getNow()+"' order by b.publish_time desc limit 50) as aa )  order by rand() desc limit " + limit;
		return getDetailsData(sql);
	}
	
	public static List<ArticleTo> getDetailsData(String sql) {
		List<ArticleTo> articles = new ArrayList<ArticleTo>();
		try {
			Connection connection = DbUtil.getConn();
			Statement statement = connection.createStatement();
			ResultSet rs = statement.executeQuery(sql);
			while (rs.next()) {
				ArticleTo articleTo = new ArticleTo();
				articleTo.setId(rs.getLong("id"));
				articleTo.setTitle(rs.getString("title"));
				articleTo.setContent(replaceContent(rs.getString("content")));
				articleTo.setPublishTime(rs.getDate("publish_time"));
				articleTo.setCategoryCode(rs.getString("category_code"));
				articleTo.setCategoryName(rs.getString("category_name"));
				articleTo.setTypeId(rs.getLong("type_id"));
				articleTo.setCategoryId(rs.getLong("category_id"));
				articleTo.setSummary(rs.getString("summary"));
				if(null == articleTo.getSummary() || articleTo.getSummary().length() < 1) {
					String content = HtmlUtil.delHTMLTag(articleTo.getContent());
					articleTo.setSummary(content.substring(0, Math.min(200, content.length())));
				}
				String typeCode = rs.getString("type_code");
				articleTo.setTypeCode(isEmpty(typeCode) ? articleTo.getCategoryCode() : typeCode);
				articleTo.setTypeName(rs.getString("type_name"));
				
				articles.add(articleTo);
			}
			rs.close();
			statement.close();
//			connection.close();
		} catch (Exception e) {
			e.printStackTrace();
			// 关闭连接
			DbUtil.closeConn();
		}
		return articles;
	}
	
	public static Map<Long, Category> getTypes() {
		try {
			Connection connection = DbUtil.getConn();
			Statement statement = connection.createStatement();
			String sql = "select * from d_category order by parent_id asc";
			ResultSet rs = statement.executeQuery(sql);
			while(rs.next()) {
				Category category = new Category();
				category.setId(rs.getLong("id"));
				category.setName(rs.getString("name"));
				category.setKeywords(rs.getString("keywords"));
				category.setDescMeta(rs.getString("descMeta"));
				category.setSummary(rs.getString("summary"));
				category.setCode(rs.getString("kc_code"));
				category.setParentId(rs.getInt("parent_id"));
				cats.put(category.getId(), category);
			}
			rs.close();
			statement.close();
		} catch(Exception e) {
			e.printStackTrace();
			// 关闭连接
			DbUtil.closeConn();
		}
		return cats;
	}

	public static void main(String[] args) throws Exception {
		createHtml();
	}
	
	public static boolean isEmpty(String str) {
		return null == str || str.length() == 0;
	}
	
	public static void createFile(String content, String fileName) throws Exception {
		String filePath = baseHtmlPath + fileName;
        //得到写文件路径
        File file = new File(filePath.substring(0, filePath.lastIndexOf("/")));
        //如果目录不存在，则创建目录
        if(!file.exists())  file.mkdirs();
        //写文件
        OutputStreamWriter out = new OutputStreamWriter(new FileOutputStream(new File(filePath)),"utf-8");
//        publishHtmlPath = HtmlUtil.getPath(baseHtmlPath);
//        String tempFilePath =  publishHtmlPath + fileName;
//        //得到写文件路径
//        File file1 = new File(tempFilePath.substring(0, tempFilePath.lastIndexOf("/")));
//        //如果目录不存在，则创建目录
//        if(!file1.exists())  file1.mkdirs();
//        //写如待上传的目录文件
//        OutputStreamWriter out1 = new OutputStreamWriter(new FileOutputStream(new File(tempFilePath)),"utf-8");
//        out1.write(content);
//        out1.flush();
//        out1.close();
        
        out.write(content);
        out.flush();
        out.close();
    }
	/**
	 * 判断是否在范围内，在则返回true
	 * @param current	当前数据
	 * @param min 最小
	 * @param max	最大
	 * @return
	 */
	public static boolean rangeInDefined(double current, double min, double max) {
		return Math.max(min, current) == Math.min(current, max);
	}
	
	public static String getIds(List<ArticleTo> artis) {
		// 获取IDS
		StringBuffer ids = new StringBuffer();
		for(ArticleTo arti : artis) {
			ids.append(arti.getId() + ",");
		}
		ids.append(",");
		return ids.toString().replace(",,", "");
	}
	
	private static List<ArticleTo> getImgByWidth(List<ArticleTo> articles, int w, int num) {
		List<ArticleTo> artis = new ArrayList<ArticleTo>();
		for(ArticleTo arti : articles) {
			if(arti.getImgWidth() >= w) {
				artis.add(arti);
				num--;
			}
			if(num == 1) break;
		}
		return artis;
	}
	
	private static String replaceContent(String content) {
		return content.replaceAll("小绵羊开车技巧网", "").replaceAll("http://kaichejiqiao.cn/skill/list_skill_1.html", "http://kc890.com/jiqiao/").replaceAll("kaichejiqiao.cn", "kc890.com");
	}
}
